﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SlamCms.SharePoint.UserManagement.Data
{
	internal static class Queries
	{
		public static string AddIsSynchedColumn
		{
			get
			{
				return @"begin
							if NOT EXISTS(
							select	1
							FROM	information_schema.columns c                
							WHERE (c.table_schema + '.' +  c.table_name) = '{0}.{1}' and
							c.column_name = 'IsSynched')
							ALTER TABLE [{0}].[{1}] ADD
								IsSynched bit NOT NULL CONSTRAINT DF_{1}_IsSynched DEFAULT 0
						end";
			}
		}

		public static string AddSynchUpdateTrigger
		{
			get
			{
				return @"CREATE TRIGGER [{0}].{1}Synched 
							ON  [{0}].[{1}] 
							AFTER INSERT, UPDATE
						AS 
						BEGIN

							update a 
							set		a.IsSynched = 1
							from	[{0}].[{1}] a, inserted i
							where	a.[{2}] = i.[{2}] and
									a.[{3}] = i.[{3}]
						END";
			}
		}

		public static string ClearNonSynchedData
		{
			get
			{
				return @"delete from [{0}].[{1}] where IsSynched = 0";
			}
		}

		public static string ClearNonSynchedSiteCollectionUserGroupData
		{
			get
			{
				return @"delete from [{0}].[{1}] where IsSynched = 0 and GroupID in (select GroupID from GroupSite gs, SLAM.Site s where s.SiteID = gs.SiteID and s.SiteCollectionID = '{2}')";
			}
		}

		public static string ClearNonSynchedSiteCollectionGroupSiteData
		{
			get
			{
				return @"delete from [{0}].[{1}] where IsSynched = 0 and SiteID in (select SiteId from SLAM.Site where SiteCollectionID = '{2}')";
			}
		}

		public static string CreateGroupSiteTable
		{
			get
			{
				return @"CREATE TABLE [{0}].[GroupSite](
							[GroupID] [int] NOT NULL,
							[SiteID] [uniqueidentifier] NOT NULL,
						 CONSTRAINT [PK_GroupSite] PRIMARY KEY CLUSTERED 
						(
							[GroupID] ASC,
							[SiteID] ASC
						)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]

						ALTER TABLE [{0}].[GroupSite]  WITH CHECK ADD  CONSTRAINT [FK_GroupSite_Site] FOREIGN KEY([SiteID])
						REFERENCES [SLAM].[Site] ([SiteID]) on delete cascade

						ALTER TABLE [{0}].[GroupSite] CHECK CONSTRAINT [FK_GroupSite_Site]

						ALTER TABLE [{0}].[GroupSite]  WITH CHECK ADD  CONSTRAINT [FK_GroupSite_Group] FOREIGN KEY([GroupID])
						REFERENCES [{0}].[Group] ([GroupID]) on delete cascade

						ALTER TABLE [{0}].[GroupSite] CHECK CONSTRAINT [FK_GroupSite_Group]";
			}
		}

		public static string CreateGroupTable
		{
			get
			{
				return @"CREATE TABLE [dbo].[Group](
							[GroupID] [int] NOT NULL,
							[Name] [varchar](255) NOT NULL,
							[View] [bit] NOT NULL,
							[Add] [bit] NOT NULL,
							[Edit] [bit] NOT NULL,
							[Approve] [bit] NOT NULL,
						 CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED 
						(
							[GroupID] ASC
						)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]";
			}
		}

		public static string CreateUserGroupTable
		{
			get
			{
				return @"begin
							CREATE TABLE [{0}].[UserGroup](
								[UserID] [int] NOT NULL,
								[GroupID] [int] NOT NULL,
							 CONSTRAINT [PK_UserGroup] PRIMARY KEY CLUSTERED 
							(
								[UserID] ASC,
								[GroupID] ASC
							)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
							) ON [PRIMARY]

							ALTER TABLE [{0}].[UserGroup]  WITH CHECK ADD  CONSTRAINT [FK_UserGroup_Group] FOREIGN KEY([GroupID])
							REFERENCES [{0}].[Group] ([GroupID]) on delete cascade

							ALTER TABLE [{0}].[UserGroup] CHECK CONSTRAINT [FK_UserGroup_Group]

							ALTER TABLE [{0}].[UserGroup]  WITH CHECK ADD  CONSTRAINT [FK_UserGroup_User] FOREIGN KEY([UserID])
							REFERENCES [{0}].[User] ([UserID]) on delete cascade

							ALTER TABLE [{0}].[UserGroup] CHECK CONSTRAINT [FK_UserGroup_User]
						end";
			}
		}

		public static string RemoveIsSynchedColumn
		{
			get
			{
				return @"begin
							ALTER TABLE [{0}].[{1}] DROP CONSTRAINT DF_{1}_IsSynched 
							ALTER TABLE [{0}].[{1}] DROP COLUMN IsSynched 
						end";
			}
		}

		public static string RemoveSynchUpdateTrigger
		{
			get
			{
				return @"DROP TRIGGER [{0}].[{1}Synched]";
			}
		}

		public static string UpdateIsSynched
		{
			get
			{
				return @"update [{0}].{1}
						set IsSynched = 1
						where {2} = '{4}' and {3} = '{5}'";
			}
		}

		public static string VerifySynchUpdateTriggerExists
		{
			get
			{
				return @"SELECT count(object_id) FROM sys.triggers WHERE object_id = OBJECT_ID(N'{0}.{1}Synched')";
			}
		}

		public static string VerifyTableExists
		{
			get
			{
				return @"select	count(table_name)
						FROM	information_schema.tables
						where	table_schema = '{0}' and
								table_name = '{1}'";
			}
		}
	}
}
